IF EXISTS (SELECT *
           FROM INFORMATION_SCHEMA.ROUTINES
           WHERE ROUTINE_NAME='pInProdDetail_Add'
           AND ROUTINE_SCHEMA='dbo'
           AND ROUTINE_TYPE='PROCEDURE'
          )
          DROP PROCEDURE dbo.pInProdDetail_Add
GO

CREATE PROC dbo.pInProdDetail_Add
	@productId INT,
	@amount INT,
	@inputPrice INT,
	@createdBy INT,
	@inPaperId INT,
	@date DATETIME
AS
/*@@*_____________________________________________________________________

  

_____________________________________________________________________*@@*/

/*______________________ CONSTANT DECLARATIONS _________________________*/


/*______________________ VARIABLE DECLARATIONS _________________________*/

  DECLARE
    @intErrNo       INT,
    @strErrMsg      NVARCHAR(400),
    @intReturn      INT,
	@id INT,
	@intBillDetail_InProdDetailId		INT,
	@intillDetail_InProdDetailAmount	INT,
	@intBillDetailId					INT

/*_______________________________ INIT _________________________________*/

  -- Setting NOCOUNT ON prevents additional recordsets with the
  -- "number of records affected" from being returned, which is not
  -- handled the same by all providers, such as for OLEDB vs. ODBC.
  SET NOCOUNT ON

/*________________________ VALIDATE PARAMETERS _________________________*/


/*_______________________________ MAIN _________________________________*/
	BEGIN TRAN AddProdDetail
	BEGIN TRY		
		
		IF NOT EXISTS (SELECT ''FROM InOutProdPaper WHERE Id = @inPaperId)
		BEGIN
			INSERT INTO InOutProdPaper (Date, CreatedBy, [Type])
			VALUES (@date,@createdBy,0)
			SET	@inPaperId = SCOPE_IDENTITY()
		END
		
			INSERT INTO InOutProdDetail (ProductId, Amount, InputPrice, InOutProdPaperId, RemainAmount)
			VALUES (@productId,@amount,@inputPrice,@inPaperId,@amount)
			SET @id = SCOPE_IDENTITY()
		
			UPDATE Product
			SET Amount = (Amount + @amount)
			WHERE Id = @productId
			
			-- Update BillDetail_InProDetail table
			WHILE @amount > 0 BEGIN
				SELECT TOP 1
					@intBillDetail_InProdDetailId = BillDetail_InProdDetail.Id,
					@intillDetail_InProdDetailAmount = BillDetail_InProdDetail.Amount,
					@intBillDetailId = BillDetail_InProdDetail.BillDetailId
				FROM BillDetail_InProdDetail
				
				INNER JOIN BillDetail
					ON BillDetail_InProdDetail.BillDetailId = BillDetail.Id
				
				WHERE BillDetail_InProdDetail.InProdDetailId IS NULL
					AND BillDetail.ProductId = @productId		
				ORDER BY BillDetail_InProdDetail.BillDetailId
				
				IF @intBillDetail_InProdDetailId IS NOT NULL BEGIN
					-- Update amount of BillDetail_InProdDetail
					UPDATE BillDetail_InProdDetail
					SET InProdDetailId = @id,
						Amount = CASE
									WHEN Amount > @amount THEN @amount
									ELSE Amount
								 END
					WHERE Id = @intBillDetail_InProdDetailId
					
					IF @intillDetail_InProdDetailAmount > @amount BEGIN
						-- If input amount has not full-fill amount of BillDetail_InProdDetail
						-- Inser new BillDetail_InProdDetail with remain amount
						INSERT INTO BillDetail_InProdDetail (
							BillDetailId,
							InProdDetailId,
							Amount
						) VALUES (
							@intBillDetailId,
							NULL,
							@intillDetail_InProdDetailAmount - @amount
						)
					END
					
					SET @amount = @amount - @intillDetail_InProdDetailAmount
					IF @amount <= 0 BEGIN
						-- Update remain amount of input
						UPDATE InOutProdDetail
						SET RemainAmount = 0
						WHERE Id = @id
					END
					
					SET @intBillDetail_InProdDetailId = NULL
					SET @intillDetail_InProdDetailAmount = 0
				END
				ELSE BEGIN
					-- Update remain amount of input
					UPDATE InOutProdDetail
					SET RemainAmount = @amount
					WHERE Id = @id
					
					SET @amount = 0 -- break while
				END
			END -- End While
			
		COMMIT TRANSACTION AddProdDetail
			
	END TRY
	BEGIN CATCH
	  ROLLBACK TRANSACTION AddProdDetail
	END CATCH
		
	SELECT @id Id,
			@inPaperId InProdPaperId
			
  -- Return value 0 indicates success
  SET @intReturn = 0

lblExit:
	RETURN @id 
/*___________________________ ERROR HANDLER ____________________________*/
lblError:
  -- An error has occurred.  On entering this section @intErrNo should
  -- already hold the error number

  -- By default, if there are any open transactions then roll them back
  IF (@@TRANCOUNT > 0) BEGIN
    ROLLBACK TRAN

  END

  -- If return value has not been explicitly set, then use the error
  -- number as return value to signify failure
  IF ((@intReturn IS NULL) OR (@intReturn = 0)) BEGIN
    SET @intReturn = @intErrNo

  END

 GOTO lblExit


